<?php

namespace app\admin\controller;

use think\Controller;
use think\Db;
use think\queue\job\Database;
use think\Request;

class Mysql extends Common
{
    /**
     * 数据表设计
     * */
    public function databaseList()
    {
        $list = Db::name('mysql_db')->paginate();

        $this->assign('list', $list);
        $this->assign('title', '数据表设计');
        return $this->view->fetch();
    }

    /**
     * 新增/编辑 数据表
     * */
    public function doDatabase()
    {
        if (Request()->isPost()) {
            $input = Request()->param();

            $id = $input['id'];
            $name = $input['name'];
            $remark = $input['remark'];

            if (empty($name)) adminReturn(2, '请输入表名');

            $where['name'] = $name;
            if ($id) $where['id'] = ['neq', $id];

            $count = Db::name('mysql_db')->where($where)->count();
            if ($count > 0) adminReturn(2, '表名已存在');

            $data = [
                'name' => $name,
                'remark' => $remark
            ];
            if ($id) {
                $res = Db::name('mysql_db')->where(['id' => $id])->update($data);
                $title = '编辑';
            } else {
                $res = Db::name('mysql_db')->insert($data);
                $title = '新增';
            }

            if ($res) {
                adminReturn(1, $title . '成功', url('databaseList'));
            } else {
                adminReturn(2, $title . '失败');
            }
        } else {
            $id = Request()->param('id');

            $title = '新增数据库';
            if ($id) {
                $info = Db::name('mysql_db')->where(['id' => $id])->find();
                $title = '编辑数据库';
            }

            $this->assign('info', $info);
            $this->assign('title', $title);
            return $this->view->fetch();
        }
    }

    /**
     * 保存设计表
     * */
    public function doTable()
    {
        $input = Request()->param();

        $db_id = $input['db_id'];
        $table_js = $input['table_js'];

        if (empty($db_id)) adminReturn(2, '数据库ID缺失');

        $result = $this->checkField($table_js);
        if ($result !== true) adminReturn(2, $result);

        if ($table_js) {
            Db::startTrans();

            try {
                $field_list = Db::name('mysql_field')->where(['db_id' => $db_id])->select();
                if (empty($field_list)) {
                    // 第一次设计表
                    $data = [];

                    foreach ($table_js as &$v) {
                        $data[] = [
                            'db_id' => $db_id,
                            'field' => $v['field'],
                            'type' => $v['type'],
                            'length' => $v['length'],
                            'spot' => $v['spot'] ? : 0,
                            'not_null' => $v['not_null'],
                            'is_primary' => $v['is_primary'],
                            'remark' => $v['remark'],
                            'default' => $v['default'],
                            'is_auto_increase' => $v['is_auto_increase'],
                            'is_unsigned' => $v['is_unsigned'],
                        ];
                    }

                    Db::name('mysql_field')->insertAll($data);

                    $res = $this->addTable($db_id, $data);
                    if ($res !== true) adminReturn(2, $res);
                } else {
                    // 编辑设计表
                    foreach ($table_js as &$v) {
                        if ($v['status'] == 'add') {
                            // 新增字段
                            unset($v['status']);
                            $v['db_id'] = $db_id;
                            Db::name('mysql_field')->insert($v);

                            $res = $this->insertField($db_id, $v);
                            if ($res !== true) adminReturn(2, $res);
                        } elseif ($v['status'] == 'delete') {
                            // 删除字段
                            $old_field = $v['field'];
                            $res = $this->deleteField($db_id, $old_field);
                            if ($res !== true) adminReturn(2, $res);

                            Db::name('mysql_field')->where(['db_id' => $db_id, 'field' => $v['field']])->delete();
                        } elseif ((strpos($v['status'], 'edit-') !== false)) {
                            // 编辑字段
                            $id = $v['id'];
                            $status = $v['status'];
                            $field = 'id,field,type,length,spot,not_null,is_primary,remark,default,is_auto_increase,is_unsigned';
                            $field_info = Db::name('mysql_field')->field($field)->where(['id' => $id])->find();

                            // 修改字段
                            unset($v['status']);
                            if ($field_info != $v) {
                                $old_field = str_replace('edit-', '', $status);
                                $res = $this->updateField($db_id, $v, $old_field);
                                if ($res !== true) adminReturn(2, $res);

                                unset($v['id']);
                                Db::name('mysql_field')->where(['id' => $id])->update($v);
                            }
                        }
                    }
                }

                Db::commit();
                adminReturn(1, '保存成功', url('databaseList'));
            } catch (\Exception $e) {
                Db::rollback();
                adminReturn(2, $e);
            }
        }
    }

    /**
     * 设计表
     * */
    public function addTable($db_id, $data)
    {
        if (empty($db_id)) return '数据库ID缺失';
        if (empty($data)) return '字段信息不存在';

        $table_info = Db::name('mysql_db')->where(['id' => $db_id])->find();
        $table_name = $table_info['name'];
        $table_remark = $table_info['remark'];

        $start_sql = "CREATE TABLE `" . Config('database.database') . "`.`" . $table_name . "`  (";
        $end_sql = ") ENGINE=InnoDB COMMENT='" . $table_remark . "';";

        $field_sql = '';
        $primary_sql = '';
        foreach ($data as $k => $v) {
            $field_sql .= ($k > 0 ? PHP_EOL : '')
                          . "`" . $v['field'] . "` "// 字段名
                          . ($v['type'] . " (" . $v['length'] . ($v['type'] == 'decimal' && $v['spot'] > 0 ? (', ' . $v['spot'] . ')') : ')')) // 类型 + 长度 + 小数点
                          . ($v[''] ? ' unsigned' : '') // 是否为负
                          . ($v['not_null'] ? ' NOT NULL' : '') // 不是null
                          . (!$v['is_primary'] ? (" DEFAULT " . ($v['default'] !== '' ? "'" . $v['default'] . "' " : "''")) : '') // 默认值（主键不存在默认值）
                          . (" COMMENT " . ($v['remark'] ? "'" . $v['remark'] . "'" : "''")) // 注释
                          . ($v['is_auto_increase'] ? ' AUTO_INCREMENT' : '') // 自动递增
                          . ",";

            if ($v['is_primary']) $primary_sql = 'PRIMARY KEY (`' . $v['field'] . '`)'; // 主键
        }

        $sql = $start_sql . PHP_EOL . $field_sql . PHP_EOL . $primary_sql . PHP_EOL . $end_sql;

        $result = file_put_contents(ROOT_PATH . 'app/command/database/' . $table_name . '.sql', $sql . PHP_EOL . PHP_EOL, FILE_APPEND);
        if ($result) {
            return true;
        } else {
            return '生成设计表SQL文件失败';
        }
    }

    /**
     * 生成新增字段SQL
     * */
    public function insertField($db_id, $data)
    {
        $table_name = Db::name('mysql_db')->where(['id' => $db_id])->value('name');

        $sql = PHP_EOL . "ALTER TABLE `" . Config('database.database') . "`.`" . $table_name
               . "` ADD COLUMN `" . $data['field'] . "` "// 字段名
               . ($data['type'] . "(" . $data['length'] . ($data['type'] == 'decimal' && $data['spot'] > 0 ? (', ' . $data['spot'] . ')') : ')')) // 类型 + 长度 + 小数点
               . ($data[''] ? ' unsigned ' : ' ') // 是否为负
               . ($data['not_null'] ? ' NOT NULL' : ' ')  // 不是null
               . (!$data['is_primary'] ? (" DEFAULT " . ($data['default'] !== '' ? "'" . $data['default'] . "' " : "'' ")) : '')  // 默认值（主键不存在默认值）
               . " COMMENT '" . $data['remark'] . "'" // 注释
               . ($data['is_auto_increase'] ? ' AUTO_INCREMENT' : '') // 自动递增
               . ($data['is_primary'] ? ", ADD PRIMARY KEY (`". $data['field'] ."`);" : ';');// 添加主键

               $result = file_put_contents(ROOT_PATH . 'app/command/database/' . $table_name . '.sql', $sql . PHP_EOL, FILE_APPEND);
        if ($result) {
            return true;
        } else {
            return '生成新增字段SQL文件失败';
        }
    }

    /**
     * 生成编辑字段sql
     * */
    public function updateField($db_id, $data, $old_field)
    {
        $table_name = Db::name('mysql_db')->where(['id' => $db_id])->value('name');

        $colume_field = $old_field == $data['field'] ? (" MODIFY COLUMN `" . $old_field . "` ") : " CHANGE COLUMN `" . $old_field . "` `" . $data['field'] . "` ";
        $sql = PHP_EOL . "ALTER TABLE `" . Config('database.database') . "`.`" . $table_name . "`"
               . $colume_field
               . ($data['type'] . "(" . $data['length'] . ($data['type'] == 'decimal' && $data['spot'] > 0 ? (', ' . $data['spot'] . ') ') : ')')) // 类型 + 长度 + 小数点
               . ($data[''] ? ' unsigned' : '') // 是否为负
               . ($data['not_null'] ? ' NOT NULL' : '')  // 不是null
               . (!$data['is_primary'] ? (" DEFAULT " . ($data['default'] !== '' ? "'" . $data['default'] . "'" : "''")) : '')  // 默认值（主键不存在默认值）
               . " COMMENT '" . $data['remark'] . "'"; // 注释

        $is_primary = Db::name('mysql_field')->where(['db_id' => $db_id, 'field' => $old_field])->value('is_primary');

        // 删除主键
        if ($is_primary && !$data['is_primary']) $sql .= ', DROP PRIMARY KEY';
        // 设置主键
        if (!$is_primary && $data['is_primary']) $sql .= ', ADD PRIMARY KEY (`' . $data['field'] . '`)';
        $sql .= ';';

        $result = file_put_contents(ROOT_PATH . 'app/command/database/' . $table_name . '.sql', $sql . PHP_EOL, FILE_APPEND);
        if ($result) {
            return true;
        } else {
            return '生成编辑字段SQL文件失败';
        }
    }

    /**
     * 生成删除字段sql
     * */
    public function deleteField($db_id, $old_field)
    {
        if($old_field){
            $table_name = Db::name('mysql_db')->where(['id' => $db_id])->value('name');
            $is_primary = Db::name('mysql_field')->where(['db_id' => $db_id, 'field' => $old_field])->value('is_primary');

            $sql = PHP_EOL . "ALTER TABLE `" . Config('database.database') . "`.`" . $table_name . "`"
                   . " DROP COLUMN `" . $old_field . "`"
                   . ($is_primary ? ", DROP PRIMARY KEY;" : ";");

            $result = file_put_contents(ROOT_PATH . 'app/command/database/' . $table_name . '.sql', $sql . PHP_EOL, FILE_APPEND);
            if ($result) {
                return true;
            } else {
                return '删除字段SQL文件失败';
            }
        }
    }

    /**
     * 生成删除数据表sql
     * */
    public function deleteTable($db_id)
    {
        $field_count = Db::name('mysql_field')->where(['db_id' => $db_id])->count();
        if($field_count){
            $table_name = Db::name('mysql_db')->where(['id' => $db_id])->value('name');

            $sql = PHP_EOL . "DROP TABLE `" . Config('database.database') . "`.`" . $table_name . "`;";
            $result = file_put_contents(ROOT_PATH . 'app/command/database/' . $table_name . '.sql', $sql . PHP_EOL, FILE_APPEND);
            if ($result) {
                return true;
            } else {
                return '删除数据表SQL文件失败';
            }
        }else{
            return true;
        }
    }

    /**
     * 校验字段规则是否正确
     * */
    public function checkField($table_js)
    {
        if ($table_js) {
            $field_arr = array_column($table_js, 'field');
            if (count(array_unique($field_arr)) != count($field_arr)) return '字段名重复';

            $primary_arr = array_column($table_js, 'is_primary');
            $count_arr = array_count_values($primary_arr);
            if ($count_arr[1] > 1) return '不可设置多个主键';

            foreach ($table_js as $v) {
                if ($v['status'] != 'delete') {
                    if ($v['field'] == '') return '检测到空字段名';
                    if ($v['length'] <= 0 || $v['length'] == '') return $v['field'] . '字段长度错误';
                }
            }
        } else {
            return '数据库字段不能为空';
        }

        return true;
    }

    /**
     * 设计表数据回显
     * */
    public function getFieldList()
    {
        $id = Request()->param('id');

        if (empty($id)) apiReturn(2, '数据库ID缺失');

        $field = 'id,field,type,length,spot,not_null,is_primary,remark,default,is_auto_increase,is_unsigned';
        $field_list = Db::name('mysql_field')->field($field)->where(['db_id' => $id])->order('is_primary desc, id asc')->select();
        if ($field_list) {
            foreach ($field_list as &$v) {
                $v['status'] = 'waitEdit';
            }
            apiReturn(1, '获取数据成功', $field_list);
        } else {
            apiReturn(2, '暂无数据');
        }
    }

    /**
     * 删除数据表
     * */
    public function delDatabase()
    {
        $id = Request()->param('id');

        if (empty($id)) adminReturn(2, '数据表ID缺失');

        Db::startTrans();
        try {
            $info = Db::name('mysql_db')->where(['id' => $id])->find();
            if ($info) {
                $res = $this->deleteTable($id);
                if ($res !== true) adminReturn(2, $res);

                Db::name('mysql_db')->where(['id' => $id])->delete();

                Db::name('mysql_field')->where(['db_id' => $id])->delete();

                Db::commit();
                adminReturn(1, '删除成功');
            }
        } catch (\Exception $e) {
            Db::rollback();
            adminReturn(2, '系统繁忙，请稍后再试~');
        }
    }
}
